from datetime import datetime
from typing import List, Tuple, Dict, Any, Optional
from fastapi import HTTPException, UploadFile
from tortoise.contrib.pydantic import pydantic_model_creator, PydanticModel
from model.sysMenuModel import SysMenu
from schema.sysMenuSchema import SysMenuCreate, SysMenuUpdate, PageParams
from io import BytesIO
from excel.sysMenuExcel import SysMenuExcel
from dataclasses import dataclass
from tortoise import connections

TYPE_DIR = 'M'
TYPE_MENU = 'C'
NO_FRAME = 1
LAYOUT = 'Layout'
PARENT_VIEW = 'ParentView'
INNER_LINK = 'InnerLink'


@dataclass
class MetaVO:
    """Meta information for router"""
    title: str
    icon: str
    no_cache: bool = None
    link: str = None

    def to_dict(self) -> Dict[str, Any]:
        """Convert to dictionary, excluding None values"""
        result = {
            "title": self.title,
            "icon": self.icon
        }
        if self.no_cache is not None:
            result["noCache"] = self.no_cache
        if self.link is not None:
            result["link"] = self.link
        return result


class PydanticConfig:
    alias_generator = lambda x: ''.join(word.capitalize() if i else word for i, word in enumerate(x.split('_')))
    allow_population_by_field_name = True


SysMenu_Pydantic = pydantic_model_creator(SysMenu, name="SysMenu", config_class=PydanticConfig)


class SysMenuService:
    @staticmethod
    async def create(data: SysMenuCreate, creator: str) -> SysMenu:
        """Create new record"""
        data_dict = data.dict(exclude_unset=True)
        data_dict["create_by"] = creator
        data_dict["create_time"] = datetime.now()
        data_dict["status"] = '0'

        obj = await SysMenu.create(**data_dict)
        return await SysMenu_Pydantic.from_tortoise_orm(obj)

    @staticmethod
    async def get_by_id(id: int) -> SysMenu:
        """Get record by ID"""
        obj = await SysMenu.get_or_none(menu_id=id, status='0')
        if not obj:
            raise HTTPException(status_code=404, detail="菜单权限表不存在")
        return await SysMenu_Pydantic.from_tortoise_orm(obj)

    @staticmethod
    async def update(id: int, data: SysMenuUpdate, updater: str) -> SysMenu:
        """Update record"""
        obj = await SysMenu.get_or_none(menu_id=id, status='0')
        if not obj:
            raise HTTPException(status_code=404, detail="菜单权限表不存在")

        update_dict = data.dict(exclude_unset=True)
        update_dict["update_by"] = updater
        update_dict["update_time"] = datetime.now()

        await obj.update_from_dict(update_dict).save()
        return await SysMenu_Pydantic.from_tortoise_orm(obj)

    @staticmethod
    async def delete(ids: List[int], updater: str) -> int:
        """Batch delete records"""
        update_data = {
            "status": '2',
            "update_by": updater,
            "update_time": datetime.now()
        }
        count = await SysMenu.filter(menu_id__in=ids, status='0').update(**update_data)
        return count

    @staticmethod
    async def get_page(params: PageParams) -> Tuple[List[SysMenu], int]:
        """Get page list"""
        query = SysMenu.filter(status='0')

        # Build query conditions

        if params.menu_name:
            query = query.filter(menu_name__icontains=params.menu_name)

        if params.path:
            query = query.filter(path__icontains=params.path)

        if params.component:
            query = query.filter(component__icontains=params.component)

        if params.query:
            query = query.filter(query__icontains=params.query)

        if params.route_name:
            query = query.filter(route_name__icontains=params.route_name)

        if params.menu_type:
            query = query.filter(menu_type__icontains=params.menu_type)

        if params.visible:
            query = query.filter(visible__icontains=params.visible)

        if params.status:
            query = query.filter(status__icontains=params.status)

        if params.use_flag:
            query = query.filter(use_flag__icontains=params.use_flag)

        if params.perms:
            query = query.filter(perms__icontains=params.perms)

        if params.icon:
            query = query.filter(icon__icontains=params.icon)

        if params.create_by:
            query = query.filter(create_by__icontains=params.create_by)

        if params.update_by:
            query = query.filter(update_by__icontains=params.update_by)

        if params.remark:
            query = query.filter(remark__icontains=params.remark)

        total = await query.count()

        records = await query.offset((params.page_num - 1) * params.page_size).limit(params.page_size).order_by(
            "order_num")

        return [await SysMenu_Pydantic.from_tortoise_orm(record) for record in records], total

    @staticmethod
    async def export_excel() -> BytesIO:
        """Export all data to Excel"""
        records = await SysMenu.filter(status='0').all()
        return await SysMenuExcel.export_data(records)

    @staticmethod
    def get_import_template() -> BytesIO:
        """Get import template"""
        return SysMenuExcel.get_import_template()

    @staticmethod
    async def import_data(file: UploadFile, creator: str) -> Tuple[int, List[str]]:
        """Import data"""
        content = await file.read()
        file_bytes = BytesIO(content)

        try:
            data_list = SysMenuExcel.parse_import_data(file_bytes)
        except ValueError as e:
            raise HTTPException(status_code=400, detail=str(e))
        except Exception as e:
            raise HTTPException(status_code=400, detail="Excel文件格式错误")

        success_count = 0
        error_msgs = []

        for index, item_data in enumerate(data_list, start=1):
            try:
                item_data['create_by'] = creator
                item_data['create_time'] = datetime.now()
                item_data['status'] = '0'

                await SysMenu.create(**item_data)
                success_count += 1

            except Exception as e:
                error_msgs.append(f"第{index}行: {str(e)}")

        return success_count, error_msgs

    @staticmethod
    async def get_tree_data(user_id: int) -> List[dict]:
        """获取用户的菜单树形结构数据"""
        menus = []

        if user_id == 1:  # 管理员用户
            menus = await SysMenu.filter(
                status='0'
            ).order_by('order_num').all()
        else:
            # 构建原生SQL查询，使用 %s 作为参数占位符
            query = """
                        SELECT DISTINCT m.*
                        FROM sys_menu m
                        INNER JOIN sys_role_menu rm ON m.menu_id = rm.menu_id  
                        INNER JOIN sys_user_role ur ON rm.role_id = ur.role_id
                        WHERE ur.user_id = %s
                        AND m.status = '0'  
                        ORDER BY m.order_num ASC
                    """

            conn = connections.get('default')
            results = await conn.execute_query_dict(query, [user_id])

            if results:
                menus = [await SysMenu_Pydantic.from_tortoise_orm(
                    await SysMenu.get_or_none(**{k: v for k, v in row.items() if k in SysMenu._meta.fields})
                ) for row in results]

        def build_tree_data(items: List[SysMenu_Pydantic], parent_id: int = 0) -> List[dict]:
            """递归构建树形结构"""
            tree_data = []

            for menu in items:
                if menu.parent_id == parent_id:
                    node = {
                        "menuId": menu.menu_id,
                        "menuName": menu.menu_name,
                        "parentId": menu.parent_id,
                        "orderNum": menu.order_num,
                        "menuType": menu.menu_type,
                        "icon": menu.icon,
                        "path": menu.path,
                        "query": menu.query,
                        "routeName": menu.route_name,
                        "isFrame": menu.is_frame,
                        "isCache": menu.is_cache,
                        "visible": menu.visible,
                        "component": menu.component
                    }

                    # 递归获取子节点
                    children = build_tree_data(items, menu.menu_id)

                    # 只有当存在子节点时才添加children字段
                    if children:
                        node["children"] = children

                    tree_data.append(node)

            # 根据 order_num 排序
            tree_data.sort(key=lambda x: x["orderNum"])

            return tree_data

        # 从根节点开始构建树形结构
        return build_tree_data(menus)

    @staticmethod
    def is_http(path: str) -> bool:
        """Check if path is an HTTP URL"""
        return path.startswith('http://') or path.startswith('https://')

    @staticmethod
    def inner_link_replace_each(path: str) -> str:
        """Replace special characters in inner link paths"""
        import re
        return re.sub(r'(http://|https://|www\.|\.|\:)', '', path)

    @staticmethod
    def is_menu_frame(menu: dict) -> bool:
        """Check if menu is a frame menu"""
        return (
                int(menu.get('parentId', 0)) == 0 and
                menu.get('menuType') == TYPE_MENU and
                menu.get('isFrame') == NO_FRAME
        )

    @staticmethod
    def is_inner_link(menu: dict) -> bool:
        """Check if menu is an inner link"""
        return (
                menu.get('isFrame') == NO_FRAME and
                SysMenuService.is_http(menu.get('path', ''))
        )

    @staticmethod
    def is_parent_view(menu: dict) -> bool:
        """Check if menu is a parent view"""
        return (
                int(menu.get('parentId', 0)) != 0 and
                menu.get('menuType') == TYPE_DIR
        )

    @staticmethod
    def get_route_name_value(name: Optional[str], path: str) -> str:
        """Get route name value"""
        router_name = name or path
        return router_name.capitalize() if router_name else ''

    @staticmethod
    def get_route_name(menu: dict) -> str:
        """Get route name for menu"""
        if SysMenuService.is_menu_frame(menu):
            return ''
        return SysMenuService.get_route_name_value(menu.get('routeName'), menu.get('path', ''))

    @staticmethod
    def get_router_path(menu: dict) -> str:
        """Get router path for menu"""
        router_path = menu.get('path', '')

        # Inner link to external website
        if int(menu.get('parentId', 0)) != 0 and SysMenuService.is_inner_link(menu):
            router_path = SysMenuService.inner_link_replace_each(router_path)

        # Non-external link and is first level directory
        elif (int(menu.get('parentId', 0)) == 0 and
              menu.get('menuType') == TYPE_DIR and
              menu.get('isFrame') == NO_FRAME):
            router_path = f"/{menu.get('path')}"

        # Non-external link and is first level menu
        elif SysMenuService.is_menu_frame(menu):
            router_path = '/'

        return router_path

    @staticmethod
    def get_component(menu: dict) -> str:
        """Get component for menu"""
        if menu.get('component') and not SysMenuService.is_menu_frame(menu):
            return menu.get('component')
        elif not menu.get('component') and int(menu.get('parentId', 0)) != 0 and SysMenuService.is_inner_link(menu):
            return INNER_LINK
        elif not menu.get('component') and SysMenuService.is_parent_view(menu):
            return PARENT_VIEW
        return LAYOUT

    @staticmethod
    def build_menus(menus: List[dict]) -> List[dict]:
        """Build frontend router menus"""
        routers = []

        for menu in menus:
            router = {
                "hidden": menu.get('visible') == '1',
                "name": SysMenuService.get_route_name(menu),
                "path": SysMenuService.get_router_path(menu),
                "component": SysMenuService.get_component(menu),
                "query": menu.get('query')
            }

            # Set meta information
            meta = MetaVO(
                title=menu.get('menuName'),
                icon=menu.get('icon'),
                no_cache=menu.get('isCache') == 0,
                link=None
            )
            router["meta"] = meta.to_dict()

            # Has children and is directory type
            if menu.get('children') and menu.get('menuType') == TYPE_DIR:
                router["alwaysShow"] = True
                router["redirect"] = 'noRedirect'
                router["children"] = SysMenuService.build_menus(menu.get('children', []))

            # Menu internal jump
            elif SysMenuService.is_menu_frame(menu):
                router["meta"] = None
                children_list = [{
                    "path": menu.get('path'),
                    "component": menu.get('component'),
                    "name": SysMenuService.get_route_name(menu),
                    "meta": MetaVO(
                        title=menu.get('menuName'),
                        icon=menu.get('icon'),
                        no_cache=menu.get('isCache') == 0,
                        link=menu.get('path')
                    ).to_dict(),
                    "query": menu.get('query')
                }]
                router["children"] = children_list

            # Inner link to external website
            elif int(menu.get('parentId', 0)) == 0 and SysMenuService.is_inner_link(menu):
                router["meta"] = MetaVO(
                    title=menu.get('menuName'),
                    icon=menu.get('icon')
                ).to_dict()
                router["path"] = '/'
                children_list = [{
                    "path": SysMenuService.inner_link_replace_each(menu.get('path', '')),
                    "component": INNER_LINK,
                    "name": SysMenuService.get_route_name(menu),
                    "meta": MetaVO(
                        title=menu.get('menuName'),
                        icon=menu.get('icon'),
                        link=menu.get('path')
                    ).to_dict()
                }]
                router["children"] = children_list

            routers.append(router)

        return routers

    async def build_tree(self, user_id: int) -> List[dict]:
        """Build menu tree for user"""
        tree_list = await self.get_tree_data(user_id)
        return self.build_menus(tree_list)

    async def get_perms(self, user_id: int) -> List[str]:
        """
        查询菜单按钮权限
        Args:
            user_id: User ID
        Returns:
            List of permission strings
        """
        menus = await self.find_list_by_user(user_id)
        if not menus:
            return []

        perms = []
        for menu in menus:
            if menu.perms:
                menu_perms = [p.strip() for p in menu.perms.split(',') if p.strip()]
                perms.extend(menu_perms)

        return perms

    @staticmethod
    async def find_list_by_user(user_id: int):
        query = """
                                SELECT DISTINCT m.*
                                FROM sys_menu m
                                INNER JOIN sys_role_menu rm ON m.menu_id = rm.menu_id  
                                INNER JOIN sys_user_role ur ON rm.role_id = ur.role_id
                                WHERE ur.user_id = %s
                                AND m.status = '0'  
                                AND m.perms is not null
                                AND m.perms != ''
                                ORDER BY m.order_num ASC
                            """

        conn = connections.get('default')
        results = await conn.execute_query_dict(query, [user_id])

        if results:
            menus = [await SysMenu_Pydantic.from_tortoise_orm(
                await SysMenu.get_or_none(**{k: v for k, v in row.items() if k in SysMenu._meta.fields})
            ) for row in results]
            return menus

        return None

    @staticmethod
    async def get_all_list():
        data_list = await SysMenu.filter(status='0').order_by('order_num').all()
        return [await SysMenu_Pydantic.from_tortoise_orm(record) for record in data_list]


    @staticmethod
    async def find_menu_by_user(user_id: int):
        query = """
                    SELECT DISTINCT m.*
                    FROM sys_menu m
                    INNER JOIN sys_role_menu rm ON m.menu_id = rm.menu_id
                    INNER JOIN sys_user_role ur ON rm.role_id = ur.role_id
                    WHERE ur.user_id = :userId 
                    AND m.status = '0'
                    ORDER BY m.order_num ASC;
                """

        conn = connections.get('default')
        return await conn.execute_query_dict(query, [user_id])

    async def get_tree_select(self, user_id: int) -> List[dict]:
        """
        Get tree-structured menu data for select components
        Args:
            user_id: User ID
        Returns:
            List of menu tree nodes
        """
        # Get menu list based on user ID
        if user_id == 1:
            menus = await self.get_all_list()
        else:
            menus = await self.find_menu_by_user(user_id)

        if not menus:
            return []

        def build_tree_data(items: List[Any], parent_id: int = 0) -> List[dict]:
            """
            Recursively build tree structure
            Args:
                items: List of menu items
                parent_id: Parent node ID
            Returns:
                List of tree nodes
            """
            tree_data = []

            for menu in items:
                # Convert to dict if it's a Pydantic model
                menu_dict = menu.dict() if hasattr(menu, 'dict') else menu

                if menu_dict.get('parent_id') == parent_id:
                    node = {
                        "id": menu_dict.get('menu_id'),
                        "label": menu_dict.get('menu_name'),
                        "value": menu_dict.get('menu_id'),
                        "parentId": menu_dict.get('parent_id'),
                        "weight": menu_dict.get('order_num')
                    }

                    # Add optional attributes if they exist
                    if menu_dict.get('icon') and menu_dict.get('icon') != '#':
                        node["icon"] = menu_dict.get('icon')

                    if menu_dict.get('menu_type'):
                        node["type"] = menu_dict.get('menu_type')

                    if menu_dict.get('path'):
                        node["path"] = menu_dict.get('path')

                    # Recursively get children
                    children = build_tree_data(items, menu_dict.get('menu_id'))

                    # Only add children if there are any
                    if children:
                        node["children"] = children

                    tree_data.append(node)

            # Sort by weight (order_num)
            return sorted(tree_data, key=lambda x: x["weight"])

        # Build tree structure starting from root nodes (parent_id = 0)
        return build_tree_data(menus)

    @staticmethod
    async def find_id_list_by_role(role_id: int):
        query = """
                    SELECT DISTINCT m.*
                    FROM sys_menu m
                    INNER JOIN sys_role_menu rm ON m.menu_id = rm.menu_id  
                    WHERE rm.role_id = %s
                    AND m.status = '0'
                    ORDER BY m.order_num ASC
                """

        conn = connections.get('default')
        results = await conn.execute_query_dict(query, [role_id])
        return [item['menu_id'] for item in results]
